package fact.view.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.sql.DataSource;

import fact.view.DTO.RoleDTO;

@ManagedBean(name = "roleModelBean")
@RequestScoped
public class RoleModelBean {
	
	@Resource(name="jdbc/factentry")
	private DataSource ds;
	
	public ArrayList<RoleDTO> getRoleList() throws SQLException
	{
		ArrayList<RoleDTO> roleList=new ArrayList<RoleDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_ROLE Order by ROLE_NAME");  
		ResultSet result =  ps.executeQuery();
		RoleDTO r=null;
				while(result.next())
				{
					r=new RoleDTO();
					r.setRoleId(result.getInt("ROLE_ID"));
					r.setRoleName(result.getString("ROLE_NAME"));
					
					roleList.add(r);
				}
		
		
		return roleList;
	}
	
	public RoleDTO getRole(String roleName) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_ROLE Where upper(ROLE_NAME)=upper(?)");  
		ps.setString(1, roleName);
		ResultSet result =  ps.executeQuery();
		RoleDTO r=null;
				if(result.next())
				{
					r=new RoleDTO();
					r.setRoleId(result.getInt("ROLE_ID"));
					r.setRoleName(result.getString("ROLE_NAME"));
					
				}
		
		return r;
	}
	
	public RoleDTO getRole(int roleId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_ROLE Where ROLE_ID=?");  
		ps.setInt(1, roleId);
		ResultSet result =  ps.executeQuery();
		RoleDTO r=null;
				if(result.next())
				{
					r=new RoleDTO();
					r.setRoleId(result.getInt("ROLE_ID"));
					r.setRoleName(result.getString("ROLE_NAME"));
					
				}
		
		
		return r;
	}
	
	public boolean addRole(String roleName) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Insert into MST_ROLE Values(SQN_ROLE.NEXTVAL,?)");  
		ps.setString(1, roleName);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	
	public boolean deleteRole(int roleId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Delete MST_ROLE Where ROLE_ID=?");  
		ps.setInt(1, roleId);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	
	public boolean editRole(String roleName,int roleId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Update MST_ROLE Set ROLE_NAME=? Where ROLE_ID=?");  
		ps.setString(1, roleName);
		ps.setInt(2, roleId);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}	

}